﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using System.Data;
using LEDShow.Helpers;

namespace LEDShow.Access
{
    class MainAccess
    {
        private int LineID = -1;

        private SqlDataLayer sqlHelper = new SqlDataLayer();

        public  MainAccess(int line)
        {
            this.LineID = line;
        }


        public DataTable GetCurrentShowDriver(int currentCls,int nextCls)
        {
            string sql_get =
                "select " +
                "distinct S.user_name as userID, " +
                "(select case   when Dgp.replaceDriver is null then  S.user_view_name  else " +
                "S.user_view_name+'（替'+(select distinct user_view_name from [system_user] where user_id=Dgp.dgrp_user_id ) +'）' end ) as userName," +
                 "P.post_type as position," +
                  "Dcom.dcom_grp_type as type," +
                    "Ctn.ctn_cls_id as cls," +
                    "Cr.number," +
                    "Cr.stringRunTrain," +
                    "dgp.attenTime as planAtten, Dgp.dgrp_attendtime as actualAtten, Dgp.dgrp_outtime as outtime ,cr.offTime as backGround,cr.reptrain " +
                    ",WT.trainNo as trainNo, WT.trainNo, cr.reptrain " +
                    " from DriverGroup Dgp left join DriverCombine Dcom on Dgp.dgrp_dcom_id = Dcom.ID" +
                    " left join ClassTurn Ctn on Dcom.dcom_team = Ctn.ctn_team and Dcom.dcom_lineid = Ctn.line " +
                    " left join CrossingRoad Cr on Dcom.dcom_cr_number = Cr.ID " +
                    " left join [system_user] S on (Dgp.replaceDriver is null and Dgp.dgrp_user_id = S.user_id) or  Dgp.replaceDriver=S.user_id" +
                    " left join Position P on S.user_post = P.ID " +
                    " left join TableInfo TI on TI.LineID=Dcom.dcom_lineid " +
                    " inner join TableToDate TT on TT.tableInfo_id=TI.ID and TT.AssignDate='" + DateTime.Now.ToString("yyyy-MM-dd") + "' " +
                    " left join Tb_WalkieTalkie WT on WT.tableToDateId=TT.ID and WT.trainNo is not null and ltrim(WT.trainNo)<> '' and ((WT.trains not like '(便)%' and substring(WT.trains,1,3)=substring(cr.reptrain,1,3)) or (WT.trains like '(便)%' and substring(WT.trains,3,5)=substring(cr.reptrain,1,3))) and WT.trainNo IS NOT NULL  " +
                    " where Dcom.dcom_lineid = " + LineID + " and ";
                            //"select" +
                            //" distinct S.user_name as userID," +
                            ////" S.user_view_name as userName," +
                            //" (select case   when Dgp.replaceDriver is null then  S.user_view_name  else " +
                            //"S.user_view_name+'（替'+(select distinct user_view_name from [system_user] where user_id=Dgp.dgrp_user_id ) +'）' end ) as userName," +
                            //" P.post_type as position," +
                            //" Dcom.dcom_grp_type as type," +
                            //" Ctn.ctn_cls_id as cls," +
                            //" Cr.number," +
                            //" Cr.stringRunTrain," +
                            //"cr.attenTime as planAtten, Dgp.dgrp_attendtime as actualAtten, Dgp.dgrp_outtime as outtime ,cr.offTime as backGround,cr.reptrain " + //add by bin 增加计划发车时间 司机实际出勤时间 司机实际退勤时间 计划退勤时间
                            //",WT.trainNo as trainNo" +
                            //" from DriverGroup Dgp left join DriverCombine Dcom on Dgp.dgrp_dcom_id = Dcom.ID" +
                            //" left join ClassTurn Ctn on Dcom.dcom_team = Ctn.ctn_team and Dcom.dcom_lineid = Ctn.line" +
                            //" left join CrossingRoad Cr on Dcom.dcom_cr_number = Cr.ID" +
                            //" left join [system_user] S on (Dgp.replaceDriver is null and Dgp.dgrp_user_id = S.user_id) or  Dgp.replaceDriver=S.user_id" +
                            //" left join Position P on S.user_post = P.ID" +
                            //" left join Tb_WalkieTalkie WT on WT.trainNo is not null and ltrim(WT.trainNo)<> '' and substring(WT.trains,1,3)=substring(cr.reptrain,1,3) and WT.trainNo IS NOT NULL  " +
                            //" left join TableToDate TT on WT.tableToDateId=TT.ID and TT.AssignDate='" + DateTime.Now.ToString("yyyy-MM-dd") + "'" +
                            //" left join TableInfo TI on TT.tableInfo_id=TI.ID  " +
                            //" and TI.LineID=Dcom.dcom_lineid" +
                            //" where Dcom.dcom_lineid = " + LineID + " and ";
           /* if (nextCls != -1)
                sql_get += "(Ctn.ctn_cls_id = " + currentCls + " or Ctn.ctn_cls_id = " + nextCls + ") order by Ctn.ctn_cls_id,Cr.number";
            else
                sql_get += "Ctn.ctn_cls_id = " + currentCls + " order by Ctn.ctn_cls_id,Cr.number";*/
            sql_get += "1=1 order by Ctn.ctn_cls_id,Cr.number";
           
           return sqlHelper.SelectDt(sql_get);
        }

        //获取车场司机（包括派班员，车场调度员，信号楼值班员，工程师)
        public DataTable GetStationDrivers()
        {
            string sql_get = "select distinct" +
               " att.place, s.user_name as UserID," +
               " s.user_view_name as UserName," +
               " P.post_type as position," +
               " att.attendTime as planAtten, att.outTime as backGround," +
               " att.actualAttendTime as actualAtten,att.actualOutTime as outTime,att.date" +
               " from tb_attendPlan att" +
               " left join [system_user] s on att.su_id=s.user_id" +
               " left join Position p on  S.user_post = P.ID" +
               " where  att.place<>'正线' and" +
               " att.date='" + DateTime.Now.ToString("yyyy-MM-dd") + "'" +
               " and s.user_line=" + LineID + "";
            return sqlHelper.SelectDt(sql_get);
        }
        public DataTable GetLeaders(int currentCls, int nextCls)
        {
            string sql_get = "select" +
                            " S.user_name as UserID," + 
                            " S.user_view_name as UserName,"+
                            " P.post_type as position," +
                            " Ctn.ctn_cls_id as cls" +
                            " from ChiefDrivers Cd left join [system_user] S on Cd.cd_user_id = S.user_id"+
                            " left join Position P on S.user_post = P.ID" +
                            " left join ClassTurn Ctn on Ctn.line = S.user_line and Cd.cd_team_id = ctn_team" +
                            " where S.user_line = " + LineID + " and ";

            if (nextCls != -1)
                sql_get += "(Ctn.ctn_cls_id = " + currentCls + " or Ctn.ctn_cls_id = " + nextCls + ")";
            else
                sql_get += "Ctn.ctn_cls_id = " + currentCls;

            return sqlHelper.SelectDt(sql_get);
        }

        public DataTable GetSupervisor(int currentCls)
        {
            string sql_get = @"SELECT [user_name] UserID,user_view_name as UserName,post.[post_name] position,Ctn.ctn_cls_id as cls
  FROM [System_User] su 
  left join dbo.Position post on post.id=su.user_post 
  left join ClassTurn Ctn on Ctn.line = su.user_line and su.user_group = ctn_team where user_post='33' and Ctn.ctn_cls_id='" + currentCls + "' and user_line='"+LineID+"'";

            return sqlHelper.SelectDt(sql_get);
        }

        public DataTable GetPBYs(int currentCls, int nextCls)
        {
            string sql_get = "select distinct" +
                            " S.user_name as UserID," +
                            " S.user_view_name as UserName," +
                            " P.post_type as position," +
                            " Ctn.ctn_cls_id as Cls,att.attendTime, att.outTime" +
                            " from tb_AttendPlan att left join [system_user] S on att.id = S.user_id" +
                            " left join Position P on S.user_post = P.ID" +
                            " left join ClassTurn Ctn on Ctn.line = S.user_line" +
                            " where S.user_line = " + LineID + " and ";

            if (nextCls != -1)
                sql_get += "(Ctn.ctn_cls_id = " + currentCls + " or Ctn.ctn_cls_id = " + nextCls + ")" + " and P.post_type='派班员'";
            else
                sql_get += "Ctn.ctn_cls_id = " + currentCls + " and P.post_type='派班员'";

            return sqlHelper.SelectDt(sql_get);
        }
        //按时间获取派班员
        //public DataTable GetPBYs()
        //{
        //    string now = DateTime.Now.ToString("yyyy-MM-dd");
        //    string sql_get = "select distinct" +
        //        " s.user_name as UserID," +
        //        " s.user_view_name as UserName," +
        //        " P.post_type as position," +
        //        " att.attendTime as planAtten , att.outTime as backGround, att.actualAttendTime as actualAtten,att.actualOutTime as outtime,att.date" +
        //        " from tb_attendPlan att" +
        //        " left join [system_user] s on att.su_id=s.user_id" +
        //        " left join Position p on  S.user_post = P.ID" +
        //        " where p.ID=11 and s.user_line="+LineID+" and att.place<>'车辆段' and "+
        //        " att.date='"+now+"'";
        //    return sqlHelper.SelectDt(sql_get);
        //}

        public DataTable GetPBYs()
        {
            string now = DateTime.Now.ToString("yyyy-MM-dd");
            string sql_get = "select distinct" +
                " s.user_name as UserID," +
                " s.user_view_name as UserName," +
                " P.post_type as position," +
                " att.attendTime as planAtten , att.outTime as backGround, att.actualAttendTime as actualAtten,att.actualOutTime as outtime,att.date" +
                " from tb_attendPlan att" +
                " left join [system_user] s on att.su_id=s.user_id" +
                " left join Position p on  S.user_post = P.ID" +
                " where p.ID=33 and s.user_line=" + LineID + " and att.place<>'车辆段' and " +
                " att.date='" + now + "'";
            return sqlHelper.SelectDt(sql_get);
        }

        /// <summary>
        /// 检查是否有安排时刻表到日期
        /// </summary>
        /// <param name="line"></param>
        /// <returns></returns>
        public bool CheckHasTimeTableToDate(ref int tableID, ref string tableName)
        {
            string today = DateTime.Now.ToString("yyyy-MM-dd");
            string sql_check = "select TTd.Tableinfo_id,TIF.TableName" +
                                " from TableToDate TTD left join TableInfo TIF on TTD.Tableinfo_id = TIF.ID" +
                                " where TIF.LineID = " + LineID + " and TTD.AssignDate = '" + today + "'";

            DataTable dt = sqlHelper.SelectDt(sql_check);
            if (null != dt && dt.Rows.Count > 0)
            {
                tableID = Convert.ToInt32(dt.Rows[0]["Tableinfo_id"]);
                tableName = dt.Rows[0]["TableName"].ToString();
            }

            return tableID == -1 ? false : true;
        }

        /// <summary>
        /// 获取时刻表所有车次的信息
        /// </summary>
        public DataTable GetAllTrainInfo(string TableName, int TableID)
        {
            string sql_traininfo = "select distinct A.TrainID,A.LastID,B.TBegin,A.Arrive,B.TEnd, DID,WT.trainNo as TrainNo from [" + TableName
                + "] A left join TrainDetailInfo B on A.TrainID = B.TrainID "+
                " left join Tb_WalkieTalkie WT on substring(WT.trains,1,3)=substring(A.TrainID,1,3) " +
                " left join TableToDate TT ON WT.tableToDateID = TT.ID and TT.tableInfo_id=B.tableInfo_id" +
                "  where A.Station = '10' and B.tableInfo_id = '" + TableID + "' order by TBegin";
            DataTable dt_traininfo = sqlHelper.SelectDt(sql_traininfo);

            return dt_traininfo;
        }

        /// <summary>
        /// 获取通知的最新更新时间
        /// </summary>
        /// <returns></returns>
        public string GetLastTime(string type)
        {
            string sql = "select top 1 nc_date from NotifyContent where ";
            switch (type)
            {
                case "注意事项":
                    sql += "nc_nt_id = 1";
                    break;
                case "出勤提醒":
                    sql += "nc_nt_id = 2";
                    break;
                case "运营目标":
                    sql += "nc_nt_id = 3";
                    break;
                case "通知":
                    sql += "nc_nt_id between 4 and 6";
                    break;
            }
            sql += " and nc_line = " + LineID + " order by cast(convert(varchar,nc_date,108) as datetime) desc"; ;

            return Convert.ToString(sqlHelper.GetSingle(sql));
        }


        //public string GetNotifyContent(string type)
        //{
        //    string sql = "select top 1 nc_content from NotifyContent where ";
        //    switch (type)
        //    {
        //        case "注意事项":
        //            sql += "nc_nt_id = 1";
        //            break;
        //        case "出勤提醒":
        //            sql += "nc_nt_id = 2";
        //            break;
        //        case "运营目标":
        //            sql += "nc_nt_id = 3";
        //            break;
        //        case "公司通知":
        //            sql += "nc_nt_id = 4";
        //            break;
        //        case "中心通知":
        //            sql += "nc_nt_id = 5";
        //            break;
        //        case "乘务部通知":
        //            sql += "nc_nt_id = 6";
        //            break;
        //    }
        //    sql += " and nc_line = " + LineID + " order by nc_date desc"; ;
        //    return Convert.ToString(sqlHelper.GetSingle(sql));
        //}

        public DataTable GetNotifyContent(string type)
        {
            string sql = "select top 1 nc_content,nc_nt_level,nc_nt_fontsize,nc_xmlcontent from NotifyContent where ";
            switch (type)
            {
                case "注意事项":
                    sql += "nc_nt_id = 1";
                    break;
                case "出勤提醒":
                    sql += "nc_nt_id = 2";
                    break;
                case "运营目标":
                    sql += "nc_nt_id = 3";
                    break;
                case "通知":
                    sql += "nc_nt_id = 4";
                    break;
                //case "中心通知":
                //    sql += "nc_nt_id = 5";
                //    break;
                //case "乘务部通知":
                //    sql += "nc_nt_id = 6";
                //    break;
            }
            sql += " and nc_line = " + LineID + " order by cast(convert(varchar,nc_date,108) as datetime) desc"; ;
            return sqlHelper.SelectDt(sql);
        }


        /// <summary>
        /// 更新天气信息
        /// </summary>
        /// <param name="address"></param>
        /// <param name="temperature"></param>
        /// <param name="weather"></param>
        /// <param name="windPower"></param>
        /// <param name="remark"></param>
        /// <returns></returns>
        public bool UpdateWeather(string address, string holiday, string temperature, string weather, string windPower, string remark)
        {
            

            string sql_update = "UPDATE [Weather] SET [Address] = '"+address+"', [holiday]='"+ holiday+"' ,[Temperature] ='"+temperature+"' ,[Weather] = '"+weather+"',[WindPower] = '"+windPower+"',[Remark] = '"+remark+"'";

            return sqlHelper.ExecuteSql(sql_update) > 0;
        }

        /// <summary>
        /// 获取天气信息
        /// </summary>
        /// <param name="address"></param>
        /// <param name="temperature"></param>
        /// <param name="weather"></param>
        /// <param name="windPower"></param>
        /// <param name="remark"></param>
        /// <returns></returns>
        public DataTable GetWeatherInfo()
        {
            string sql = "SELECT [Address],[holiday],[Temperature],[Weather],[WindPower],[Remark] FROM [Weather]";
            return sqlHelper.SelectDt(sql);
        }

        /// <summary>
        /// 获取头像信息
        /// </summary>
        /// <param name="line">所属线路</param>
        /// <returns>头像信息：远程数据库头像的MD5和头像路径信息表</returns>
        public DataTable GetUserHeadImgInfo(string line)
        {
            DataTable dt = null;

            //string sql = "select user_name, user_headImgMD5, user_img from [system_user] where (user_dep_id = 7 or user_dep_id = 4)and user_line = " + line;
            string sql = "select user_name, user_headImgMD5, user_img from [system_user] where user_line = " + line;
            dt = sqlHelper.SelectDt(sql);
            return dt;
        }

        public string GetVersion()
        {

            string sql_update = "select * from SoftVersion where Code='dp'";
            DataTable dt = sqlHelper.SelectDt(sql_update);
            if (dt.Rows.Count > 0)
            {
                return dt.Rows[0]["sVersion"].ToString();
            }
            else
            {
                return "[获取版本失败]";
            }
        }
    }
}
